package DB;

import Model.Semester;
import java.sql.*;
import java.util.ArrayList;

public class SemesterDB implements SemesterDBIF{

    private Connection conn;

    public SemesterDB() {
        conn = ConnectionDB.getInstance().getConn();
    }

    @Override
    public int insertSemester(Semester semester) {
        int rc = -1;
        PreparedStatement pstmt;

        String query="INSERT INTO Semester(semesterId, semesterNo, startDate, endDate)  VALUES(?,?,?,?)";


        System.out.println("insert : " + query);
        try{

            pstmt = conn.prepareStatement(query);
            pstmt.setQueryTimeout(5);
            pstmt.setInt(1,semester.getId());
            pstmt.setInt(2,semester.getSemesterNo());
            pstmt.setString(3, semester.getStartDate());
            pstmt.setString(4, semester.getEndDate());
            rc = pstmt.executeUpdate();
            pstmt.close();
        }
        catch(SQLException ex){
            System.out.println("SemesterNo Insert Error" + ex.getMessage());
        }
        return(rc);
    }

    @Override
    public Semester findSemester(int id) {
        return singleWhere("semesterId = " + id, false);
    }

    @Override
    public ArrayList<Semester> getAllSemesters(boolean retrieveAssociation) {
        return miscWhere("", false);
    }

    @Override
    public int updateSemester(Semester semester) {

        Semester seObj = semester;
        int rc = -1;

        String query="UPDATE Semester SET " +
                "semesterNo ='" +seObj.getSemesterNo() + "', " +
                "startDate ='" + seObj.getStartDate() + "', " +
                "endDate = '" + seObj.getEndDate() + "' " +
                "WHERE semesterId = '" + seObj.getId() + "'";

        System.out.println("Update query: " + query);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch(Exception e){
            System.out.println("Update exception in semester db: " +e);
        }
        return(rc);
    }

    @Override
    public int deleteSemester(int id) {
        int rc = -1;

        String query = "DELETE FROM Semester WHERE semesterId = '" + id + "'";

        System.out.println(query);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch(Exception e){
            System.out.println("Delete exception in semester db: " + e);
        }
        return(rc);
    }

    //Finding a single semester
    private Semester singleWhere(String wClause, boolean retrieveAssociation) {
        ResultSet results;
        Semester supObj = new Semester();

        String query = buildQuery(wClause);
        System.out.println(query);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if (results.next()) {
                supObj = buildSemester(results);
                stmt.close();
            }
            else {
                supObj = null;
            }
        }
        catch (Exception e) {
            System.out.println("Query exception: " + e);
        }

        return supObj;
    }

    //Finding all semesters
    private ArrayList<Semester> miscWhere(String wClause, boolean retrieveAssociation) {
        ResultSet results;
        ArrayList<Semester> list = new ArrayList<Semester>();

        String query = buildQuery(wClause);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while (results.next()) {
                Semester seObj = buildSemester(results);
                list.add(seObj);
            }

            stmt.close();
        }
        catch (Exception e) {
            System.out.println("Query exception: " + e);
            e.printStackTrace();
        }

        return list;
    }

    //Method to build the query
    private String buildQuery(String wClause) {
        String query = "SELECT semesterId, semesterNo, startDate, endDate FROM Semester";

        if (wClause.length()>0) {
            query = query + " WHERE " + wClause;
        }

        return query;
    }

    //Method to build a semester object
    private Semester buildSemester(ResultSet results) {

        Semester supObj = new Semester();

        try {
            supObj.setId(results.getInt("semesterId"));
            supObj.setSemesterNo(results.getInt("semesterNo"));
            supObj.setStartDate(results.getString("startDate"));
            supObj.setEndDate(results.getString("endDate"));

        }
        catch (Exception e) {
            System.out.println("Error building semester object");
        }

        return supObj;
    }

    public int semesterGetMax() {
        int nextId = GetMax.getMaxId("SELECT max(semesterId) from Semester");

        nextId = nextId +1;

        return nextId;
    }
}
